package ch6;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    private JdbcUtils jdbcUtils;
    private Connection connection;
    private PreparedStatement preparedStatement;

    public UserBean findByUserCode(String userCode, String userPassword) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "Select * from smbms_user where userCode = ? and userPassword=?";

        try {
            connection = jdbcUtils.getConnection ();
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1, userCode);
            preparedStatement.setString (2, userPassword);

            resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ()) {
                UserBean user = new UserBean ();
                user.setId (resultSet.getInt ("id"));
                user.setUserName (resultSet.getString ("userName"));
                user.setUserCode (resultSet.getString ("userCode"));
                user.setUserPassword (resultSet.getString ("userPassword"));
                user.setGender (resultSet.getInt ("Gender"));
                user.setBirthday (resultSet.getDate ("Birthday"));
                user.setAddress (resultSet.getString ("address"));
                user.setPhone (resultSet.getString ("phone"));
                user.setUserRole (resultSet.getInt ("userRole"));
                return user;

            }
            return null;

        } catch (ClassNotFoundException e) {
            throw new RuntimeException (e);
        } catch (SQLException e) {
            throw new RuntimeException (e);
        } finally {
            jdbcUtils.close (connection, preparedStatement, resultSet);
        }
    }

    public Integer deleteUserById(Integer id) {
        return null;
    }


    public Integer addUser(UserBean userBean) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        String sql = "insert  int smbms_user(userName,userCode,userPassword,gender,birthday,address,phone,userRole)" + "values (?,?,?,?,?,?,?,?,?,?)";

        try {
            connection = JdbcUtils.getConnection ();
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1, userBean.getUserName ());
            preparedStatement.setString (2, userBean.getUserCode ());
            preparedStatement.setString (3, userBean.getUserPassword ());
            preparedStatement.setInt (4, userBean.getGender ());
            preparedStatement.setDate (5, (java.sql.Date) new java.util.Date (userBean.getBirthday ().getTime ()));
            preparedStatement.setString (6, userBean.getAddress ());
            preparedStatement.setString (7, userBean.getPhone ());
            preparedStatement.setInt (8, userBean.getUserRole ());
            preparedStatement.setInt (9, userBean.getCreatedBy ());
            preparedStatement.setDate (10, (java.sql.Date) new java.util.Date (userBean.getCreatedDate ().getTime ()));

            Integer i = preparedStatement.executeUpdate ();

            return i;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException (e);
        } catch (SQLException e) {
            throw new RuntimeException (e);
        }


    }

    public List<UserBean> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from smbms_user";
        try {
            connection = JdbcUtils.getConnection ();
            preparedStatement = connection.prepareStatement (sql);
            resultSet = preparedStatement.executeQuery ();
            List<UserBean> list = new ArrayList<> ();

            while (resultSet.next ()) {
                UserBean user = new UserBean ();
                user.setId (resultSet.getInt ("id"));
                user.setUserName (resultSet.getString ("userName"));
                user.setUserCode (resultSet.getString ("userCode"));
                user.setUserPassword (resultSet.getString ("userPassword"));
                user.setGender (resultSet.getInt ("Gender"));
                user.setBirthday (resultSet.getDate ("Birthday"));
                user.setAddress (resultSet.getString ("address"));
                user.setPhone (resultSet.getString ("phone"));
                user.setUserRole (resultSet.getInt ("userRole"));
                list.add (user);

            }
            return list;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException (e);
        } catch (SQLException e) {
            throw new RuntimeException (e);
        }


    }


    public Integer deleteUser(Integer id) {

        String sql = "delete from smbms_user where id=?";
        try {
            connection = JdbcUtils.getConnection ();
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setInt (1, id);
            int result = preparedStatement.executeUpdate ();
            return result;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException (e);
        } catch (SQLException e) {
            throw new RuntimeException (e);
        }


    }

    public UserBean findById(Integer id) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "Select * from smbms_user where id=?";

        try {
            connection = jdbcUtils.getConnection ();
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setInt (1, id);


            resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ()) {
                UserBean user = new UserBean ();
                user.setId (resultSet.getInt ("id"));
                user.setUserName (resultSet.getString ("userName"));
                user.setUserCode (resultSet.getString ("userCode"));
                user.setUserPassword (resultSet.getString ("userPassword"));
                user.setGender (resultSet.getInt ("Gender"));
                user.setBirthday (resultSet.getDate ("Birthday"));
                user.setAddress (resultSet.getString ("address"));
                user.setPhone (resultSet.getString ("phone"));
                user.setUserRole (resultSet.getInt ("userRole"));
                return user;

            }
            return null;

        } catch (ClassNotFoundException e) {
            throw new RuntimeException (e);
        } catch (SQLException e) {
            throw new RuntimeException (e);
        }
    }

    public static Integer updateUser(UserBean user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        String sql = "update smbms_user set userName=?,userCode=?,userPassword=?,gender=?,birthday=?,address=?,phone=?,userRole=? where id=?";

        try {
            connection = JdbcUtils.getConnection ();
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1, user.getUserName ());
            preparedStatement.setString (2, user.getUserCode ());
            preparedStatement.setString (3, user.getUserPassword ());
            preparedStatement.setInt (4, user.getGender ());
            preparedStatement.setDate (5, (java.sql.Date) new java.util.Date (user.getBirthday ().getTime ()));
            preparedStatement.setString (6, user.getAddress ());
            preparedStatement.setString (7, user.getPhone ());
            preparedStatement.setInt (8, user.getUserRole ());
            preparedStatement.setInt (9, user.getCreatedBy ());

            return preparedStatement.executeUpdate ();

        } catch (ClassNotFoundException e) {
            throw new RuntimeException (e);
        } catch (SQLException e) {
            throw new RuntimeException (e);
        }
    }
}


